﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NRemedy;
using NRemedy.Linq;
using System.Configuration;
using Oracle.DataAccess.Client;

namespace Syncer
{
    public class EscalationImport
    {
        public Dictionary<string, Int32> _ComDic { get; set; }

        public Dictionary<string, Int32> _BsDic { get; set; }

        public void Execute(Param param)
        {
            Log.Info("start escalation import.");
            //get application config
            string arserver = ConfigurationManager.AppSettings["ARServer"].ToString();
            string user = ConfigurationManager.AppSettings["User"].ToString();
            string pwd = ConfigurationManager.AppSettings["Pwd"].ToString();

            _ComDic = param.ComDic;
            _BsDic = param.BsDic;

            using (ARLoginContext context = new ARLoginContext(arserver, user, pwd))
            {
                ARSet<HPDAssociations> incidents = new ARSet<HPDAssociations>(context, new ARServerDefaultFactory());
                var query = from i in incidents
                            where i.Modifed_Date >= param.StartTime && i.Modifed_Date <= param.EndTime
                            select i;

                ARSet<CHGAssociations> changes = new ARSet<CHGAssociations>(context, new ARServerDefaultFactory());
                var query2 = from i in changes
                            where i.Modifed_Date >= param.StartTime && i.Modifed_Date <= param.EndTime
                            select i;

                ARSet<PBMInvestigationAssociations> problems = new ARSet<PBMInvestigationAssociations>(context, new ARServerDefaultFactory());
                var query3 = from i in problems
                             where i.Modifed_Date >= param.StartTime && i.Modifed_Date <= param.EndTime
                             select i;

                using (OracleDataAccess oda = new OracleDataAccess())
                {
                    //HPD:Association
                    foreach (var incident in query)
                    {
                        string sql = "select ID from Escalation where SourceId=:sid and DestinationId=:did";
                        //incidentNumber must not be null
                        OracleParameter[] _param = new OracleParameter[] { 
                            new OracleParameter("sid",incident.RequestID01),
                            new OracleParameter("did",incident.RequestID02)
                        };
                        object requestid = oda.ExecuteScalar(sql, _param);
                        //if not exsit,should be insert
                        if (requestid == null)
                        {
                            try
                            {
                                InsertEscalation(incident.RequestID01,incident.FormName01,incident.RequestID02,incident.FormName02,incident.Create_Date, oda);
                                Log.Info("Insert " + incident.Request_ID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Insert " + incident.Request_ID + " failed.", ex);
                            }
                        }
                    }

                    //CHG:Associations
                    foreach (var change in query2)
                    {
                        string sql = "select ID from Escalation where SourceId=:sid and DestinationId=:did";
                        //incidentNumber must not be null
                        OracleParameter[] _param = new OracleParameter[] { 
                            new OracleParameter("sid",change.RequestID01),
                            new OracleParameter("did",change.RequestID02)
                        };
                        object requestid = oda.ExecuteScalar(sql, _param);
                        //if not exsit,should be insert
                        if (requestid == null)
                        {
                            try
                            {
                                InsertEscalation(change.RequestID01, change.FormName01, change.RequestID02, change.FormName02, change.Create_Date, oda);
                                Log.Info("Insert " + change.Request_ID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Insert " + change.Request_ID + " failed.", ex);
                            }
                        }
                    }


                    //PBM:Investigation Associations
                    foreach (var pbm in query3)
                    {
                        string sql = "select ID from Escalation where SourceId=:sid and DestinationId=:did";
                        //incidentNumber must not be null
                        OracleParameter[] _param = new OracleParameter[] { 
                            new OracleParameter("sid",pbm.RequestID01),
                            new OracleParameter("did",pbm.RequestID02)
                        };
                        object requestid = oda.ExecuteScalar(sql, _param);
                        //if not exsit,should be insert
                        if (requestid == null)
                        {
                            try
                            {
                                InsertEscalation(pbm.RequestID01, pbm.FormName01, pbm.RequestID02, pbm.FormName02, pbm.Create_Date, oda);
                                Log.Info("Insert " + pbm.Request_ID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Insert " + pbm.Request_ID + " failed.", ex);
                            }
                        }
                    }
                }

            }
            Log.Info("escalation import end.");
        }


        private void InsertEscalation(string sourceId,string sourceType,string desId,string desType,DateTime TicketTime, OracleDataAccess oda)
        {
            string insert_sql = @"insert into Escalation(
                                    SourceId,
                                    SourceType,
                                    DestinationId,
                                    DestinationType,
                                    TicketCreateTime,
                                    CreateTime) 
                            values (
                                        :sourceId,
                                        :sourceType,
                                        :destinationId,
                                        :destinationType,
                                        :ticketcreatetime,
                                        :createtime
                                    )";
            OracleParameter[] param = new OracleParameter[] { 
                            new OracleParameter("sourceId",sourceId),
                            new OracleParameter("SourceType",GetType(sourceType)),
                            new OracleParameter("destinationId",desId),
                            new OracleParameter("destinationType",GetType(desType)),
                            new OracleParameter("ticketcreatetime",TicketTime),
                            new OracleParameter("createtime",DateTime.Now)
                        };

            oda.ExecuteCommand(insert_sql, param);
        }

        private int? GetType(string type)
        {
            if (type == null)
                return null;
            switch (type)
            {
                case "HPD:Help Desk": return 1;
                case "CHG:Infrastructure Change": return 2;
                case "PBM:Problem Investigation": return 3;
                default: return null;
            }
 
        }

//        private void UpdateEscalation(HPDHelpDesk incident, OracleDataAccess oda)
//        {
//            string update_sql = @"update Incident set
//                                    SiteID=	:siteid,
//                                    BusinessSystemId=	:businesssystemid,
//                                    Status=	:status,
//                                    Category=	:category,
//                                    Impact=	:impact,
//                                    Severity=	:severity,
//                                    Priority=	:priority,
//                                    Summury=	:summary,
//                                    Source=	:source,
//                                    CloseCode=	:closecode,
//                                    TicketCreateTime=	:ticketcreatetime,
//                                    TicketResolvedTime=	:ticketresolvedtime,
//                                    ResolvedGroup=	:resolvedgroup,
//                                    ResolvedTimeout=	:resolvedtimeout
//                                    where Request_ID=:request_id
//                                    ";
//            OracleParameter[] param = new OracleParameter[] { 
//                            //new OracleParameter("siteid",companyDic[incident.Company]),
//                            //new OracleParameter("businesssystemid",string.IsNullOrEmpty(incident.BusinessSystem) ? null : (object)bsDic[incident.BusinessSystem + incident.Company]),
//                            new OracleParameter("status",(int)incident.Status),
//                            new OracleParameter("category",incident.Event1),
//                            new OracleParameter("impact",(int)incident.Impact),
//                            new OracleParameter("severity",(int)incident.Urgency),
//                            new OracleParameter("priority",(int)incident.Priority),
//                            new OracleParameter("summary",incident.Description),
//                            new OracleParameter("source",(int)incident.ReportedSource),
//                            new OracleParameter("closecode",(int)incident.Status_Reason),
//                            new OracleParameter("ticketcreatetime",incident.Create_Date),
//                            new OracleParameter("ticketresolvedtime",incident.Modifed_Date),
//                            new OracleParameter("resolvedgroup",HPDHelpDesk.ConvertGroup(incident.AssignedGroup)),
//                            //new OracleParameter("resolvedtimeout",incident.LastResolvedDate == new DateTime(0) ? 0 : (incident.LastResolvedDate - incident.Create_Date).TotalSeconds - (double)incident.ResovledTimeLimit),
//                            new OracleParameter("request_id",incident.IncidentNumber)
//                        };

//            var plist = param.ToList();

//            //find companyid
//            if (!_ComDic.ContainsKey(incident.Company))
//                throw new Exception("Can not match " + incident.Company);
//            plist.Add(new OracleParameter("siteid", _ComDic[incident.Company]));


//            //find businesssystemid
//            if (string.IsNullOrEmpty(incident.BusinessSystem))
//                plist.Add(new OracleParameter("businesssystemid", null));
//            else if (!_BsDic.ContainsKey(incident.BusinessSystem + incident.Company))
//                throw new Exception("Can not match " + incident.BusinessSystem + incident.Company);
//            else
//                plist.Add(new OracleParameter("businesssystemid", _BsDic[incident.BusinessSystem + incident.Company]));

//            //calculate resolvedtimeout
//            if (incident.LastResolvedDate == default(DateTime) || incident.ResovledTimeLimit == default(decimal))
//                plist.Add(new OracleParameter("resolvedtimeout", null));
//            else
//                plist.Add(new OracleParameter("resolvedtimeout", (incident.LastResolvedDate - incident.Create_Date).TotalSeconds - (double)incident.ResovledTimeLimit));

//            oda.ExecuteCommand(update_sql, plist.ToArray());
//        }
    }
}
